<?php

/**
 * linruiyan@2012-8-7 增加selectIndex方法
 */


require_once "Driver.php";

class MysqlDriver extends Driver{
	
	private $writeHandle = null;
	private $readHandle = null;
	

	private $lastSql = null;
	

	/**
	 * 构造函数，初始化插件
	 */
	public function __construct($conf) {
		$this->writeHandle = $this->readHandle = $this->connect($conf['host'],$conf['user'],$conf['pwd'],$conf['dbname']);
		parent::__construct();
	}

	public function connect($host,$user,$pwd,$dbname){
		if(!($id = @mysqli_connect('p:'.$host,$user, $pwd, $dbname))) {
			throw new PException("链接错误mysql".mysqli_error($id));
		}
		mysqli_query($id, "set profiling_history_size 100");
		mysqli_query($id, "set names UTF8");
		return $id;
	}


	public function close(){
		@mysqli_close($this->writeHandle);
		if($this->readHandle !== $this->writeHandle){
			@mysqli_close($this->readHandle);
		}
	}
	/**
	 * 执行一条sql，不返回结果
	 */
	public function exec($sql){
		// echo ('select'==substr($sql,0,6))?'r:'.$this->readHandle:'w:'.$this->writeHandle;
		// echo ':'.$sql;
		$handle = ('select'==strtolower(substr($sql,0,6)))?$this->readHandle:$this->writeHandle;
		$rs = mysqli_query($handle, $sql);
		if(!$rs){
			throw new PException("sql[{$sql}]错误：".mysqli_error($handle));
		}
		$this->lastSql = $sql;
		return $rs;

	}


	/**
	 * 查询并返回2维数组
	 */
	public function select($sql,$resultType=MYSQLI_ASSOC){
		
		$rs = array();
		$query = $this->exec($sql);

		while($item = mysqli_fetch_array($query,$resultType)){
			$rs[] = $item;
		}

		return  $rs;
		
	}
	/**
	 * linruiyan@2012-8-7 增加一个快捷函数，减少其他插件需要重新构建ID索引
	 */
	public function selectIndex($sql,$column='id',$resultType=MYSQLI_ASSOC){
		$data = $this->select($sql,$resultType);
		$rs = array();
		if(!empty($data)){
			foreach($data as $key=>$val){
				$rs[$val[$column]] = $val;
			}
		}
		return $rs;
	}
	
	/**
	 * linruiyan@2012-9-12 增加一个快捷函数，将查询结果自动按某个字段分组
	 */
	public function selectGroupBy($sql,$column,$resultType=MYSQLI_ASSOC){
		$data = $this->select($sql,$resultType);
		$rs = array();
		if(!empty($data)){
			foreach($data as $key=>$val){
				$rs[$val[$column]][] = $val;
			}
		}
		return $rs;
	}
	
	/**
	 * 为方便获取一条数据
	 */
	public function fetchFirst($sql,$resultType=MYSQLI_ASSOC){
		$rs = $this->select($sql.' limit 1',$resultType);
		return empty($rs)?array():$rs[0];
	}
	
	/**
	 * linruiyan@2012-7-19 通过快捷方式直接获取ID的数据
	 */
	public function get($table,$id,$resultType=MYSQLI_ASSOC){
		$id = mysqli_real_escape_string($this->readHandle, $id);
		$table = mysqli_real_escape_string($this->readHandle, $table);
		return $this->fetchFirst("select * from `{$table}` where id='{$id}'",$resultType);
	}

	/**
	 * fetchRow 根据条件获取一条记录 
	 * @param  string $table 表名
	 * @param  array  $where where 条件 比如: array('id' => 25, 'uid' => 100,...)
	 * @param  array  $columns 查询的字段,查询所有的字段该参数为空 比如: array('uid', 'nickname', 'email',...)
	 * @return array
	 */
	public function fetchRow($table, $where = array(), $columns = array()) {
		if(empty($table) || !is_array($where) || !is_array($columns)) return false;

		$columns = $this->_buildColumns($columns);
		
		$where = $this->_buildWhere($where);
		
		$sql = "SELECT {$columns} FROM {$table} {$where}";

		return $this->fetchFirst($sql);

	}

	/**
	 * fetchData 根据条件获取多条记录
	 * @param  string  $table 表名
	 * @param  array   $where where条件 比如: array('id' => 25, 'uid' => 100,...)
	 * @param  array   $columns 查询的字段,查询所有的字段该参数为空 比如: array('uid', 'nickname', 'email',...)
	 * @param  integer $offset 分页
	 * @param  integer $limit 返回的条数
	 * @param  string  $orderBy 按照某一个字段排序
	 * @param  string  $sortBy 是降序还是升序
	 * @return array
	 */
	public function fetchData($table, $where = array(), $columns = array(), $offset = 0, $limit = 25, $orderBy = 'id', $sortBy = 'DESC') {
		if(empty($table) || !is_array($where) || !is_array($columns) || $offset < 0 || $limit <= 0) return false;

		$columns = $this->_buildColumns($columns);

		$where = $this->_buildWhere($where);

		$sortBy = strtoupper($sortBy) == 'DESC' ? 'DESC' : 'ASC';
		$order = !empty($sortBy) && !empty($orderBy) ? " ORDER BY {$orderBy} {$sortBy}" : '';

		$sql = "SELECT {$columns} FROM {$table} {$where} {$order} LIMIT {$offset}, {$limit}";

		return $this->select($sql);
	}

	/**
	 * _buildColumns 构建查询的列  将数组转换为字符串
	 * @param  array $columns
	 * @return string
	 */
	private function _buildColumns($columns) {
		return is_array($columns) && !empty($columns) ? implode(',', $columns) : '*';
	}

	/**
	 * _buildWhere 构建查询的条件(将数组转换为字符串)
	 * @param  array $conditions
	 * @return string
	 */
	private function _buildWhere($conditions) {
		$where = ' WHERE 1 ';
		if(!empty($conditions) && is_array($conditions)) {
			foreach ($conditions as $key => $value) {
				$where .= " AND {$key} = '{$value}'"; 
			}
		}

		return $where;
	}
	
	
	
	/**
	 * 提交事务
	 */
	public function commit(){
		$this->exec("COMMIT");
	}
	
	/**
	 * 开始事务
	 */
	public function begin(){
		$this->exec("BEGIN");
	}

	/**
	 * 回滚事务
	 */
	public function back(){
		$this->exec("ROLLBACK");
	}	
	
	
	public function getLastInsertId(){
		return mysqli_insert_id($this->writeHandle);
	}
	

	/**
	 * ping
	 *
	 * ping服务器的连接
	 * 
	 * @return boolean
	 */
	public function ping() {
		return mysqli_ping($this->writeHandle) && mysqli_ping($this->readHandle);
	}




	/**
	 * 判断数据是否存在
	 */
	public function isexists($sql){
		$rs = $this->select($sql);
		return empty($rs[0])?false:true;
		
	}	
	
	/**
	 * 返回上一条sql的记录数
	 */
	public function count($sql=''){
		
		$sql = empty($sql)?$this->lastSql:$sql;
		if(empty($sql)){
			throw new PException("找不到统计sql");
		}
		//select * from table where id=23 group by id order by id limit 10,200
		//去掉limit及后面的字母
		$sql = preg_replace("/limit.*$/i",'',$sql);
		
		//截取from后的sql段
		$sql = 'select count(*) as total ' . stristr($sql,'from');
		
		$data = $this->select($sql);
		//如果内容为空时会爆未定义$data提示
		if(empty($data)){
			$rs = 0;
		}else{
			$rs = $data[0]['total'];
		}
		return intval($rs);
	}

	/**
	 * 插入多条数据
	 */
	public function batchInsert($table, $data, $exec=true) {
		if(! is_array($data)){
			throw new PException("\$data必须为数组");
		}

		if (! is_array($data[0])) {
			throw new PException("\$data的元素必须为数组");
		}

		$fields = array_keys($data[0]);
		$escapeFunction = function($data) {
			return mysqli_real_escape_string($this->writeHandle, $data);
		};
		$values = array_map(function ($row) {
			return array_map($escapeFunction, array_values($row));
		}, $data);

		$wrapper = function($text, $left, $right=null) {
			return $left.$text.(is_null($right) ? $left: $right);
		};

		$table = $wrapper($table, '`');
		$fields = $wrapper(implode('`,`', $fields), '(`', '`)');
		$values = implode(',', array_map(function ($row) use ($wrapper) {
			return $wrapper(implode('\',\'', $row), '(\'', '\')');
		}, $values));

		$sql = "INSERT INTO $table $fields VALUES $values";
		if ($exec) {
			$this->exec($sql);
			$id = $this->getLastInsertId();
			return $id;
		}
		else {
			return $sql;
		}
	}

	/**
	 * 插入一条数据
	 */
	public function insert($table,$data){
		if(!is_array($data)){
			throw new PException("\$data必须为数组");
		}
  		foreach($data as $key => $value){
   	  		$field_names[] = $key;
			//@??如果开启魔术过滤效果，数据将会被多次addslashes，需要做处理
  	  		// $field_values[] = addslashes($value);
			$field_values[] = mysqli_real_escape_string($this->readHandle, $value);
  		}
		//echo "insert into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')";
		//echo "insert into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')";
		//echo "insert into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')";
		$query = "insert into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')";
		$this->exec("insert into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')");
		$id = $this->getLastInsertId();
		//echo $id.'===';
		return $id;			
	}

	/**
	 * 保存多条数据(对replace into的封装)
	 *
	 * @param	table	string	表名
	 * @param	fields	array	字段名
	 * @param	data	array	数据列表, 数据的顺序要和fields中的字段顺序一致
	 */
	public function saveMany($table, $fields, $data) {
		if(!is_array($fields) or !is_array($data)){
			throw new PException("\$fields和\$data必须为数组");
		}
		else if (empty($fields) or empty($data)) {
			throw new PException("\$fields和\$data不能为空");
		}
		else if (count($fields) != count($data[0])) {
			throw new PException("\$fields和\$data中的数据长度不一致");
		}

		$replace = "replace into `{$table}` (`".implode('`,`', $fields).'`)';
		$buf = array();
		foreach ($data as $row) {
			$buf[] = "('".implode("','", $row)."')";
		}
		$replace .= 'values '.implode(',', $buf);

		return $this->exec($replace);
	}

	/**
	 * 保存一条数据(对replace into的封装)
	 */
	public function save($table,$data){
		if(!is_array($data)){
			throw new PException("\$data必须为数组");
		}
  		foreach($data as $key => $value){
   	  		$field_names[] = $key;
			//@??参考insert方法
			// $field_values[] = addslashes($value);
  	  		$field_values[] = mysqli_real_escape_string($this->readHandle, $value);
  		}
		
		return $this->exec("replace into `{$table}` (`".implode('`,`', $field_names)."`) values "."('".implode("','", $field_values)."')");
	}


	/**
	 * 更新一条或多条数据
	 */
	public function update($table,$data,$where=''){
  		$set_str = false;
		foreach($data as $key => $value){
			//@??参考insert方法	
			// $value=addslashes($value);
			$value=$value;	  				
  			$set_str .= $set_str ? ",":"";
			//linruiyan@2012-11-24 在插入json格式时，会变形。添加了mysql_escape_string处理
  			$set_str .= "`{$key}`='".mysqli_real_escape_string($this->readHandle, $value)."'";
  		}
  		if(!empty($where))$where = "where {$where}";
  		$sql = "update `{$table}` set {$set_str} {$where}";

  		return $this->exec($sql);
	}
	

	
	/**
	 * 删除一条(如果需要删除多条数据，那么自己写sql)
	 * linruiyan@2012-7-20 逻辑删除，如果没有字段isdel将会报错。
	 */
	public function del($table,$id){
		$sql = "update {$table} set isdel='Y' where id={$id} limit 1";
		return $this->exec($sql);
	}
	
	/**
	 * linruiyan@2012-7-20 添加彻底删除了数据。建议不要使用
	 */
	public function rm($table,$id){
		$sql = "delete from {$table} where id={$id} limit 1";
		return $this->exec($sql);
	}

	/**
	 * 对某个数据的整型值增减值操作，在很多统计数据中常被用到。所以抽出来。
	 */
	public function intOffset($table,$id,$column,$val=1){
		
		//如果是负数，要保证更新结果大于0
		if($val<0){
			$val = -$val;
			return $this->exec("update {$table} set {$column}={$column}-{$val} where id={$id} and {$column}>{$val}");
		}else{
			return $this->exec("update {$table} set {$column}={$column}+{$val} where id={$id}");
		}
	}
	
	


	private function errorlog($error){
		$data['sqlstate_code'] = $error[0];
		$data['driver_code'] = $error[1];
		$data['message'] = $error[2];
		$data['event'] = N_EVENT;
		$data['action'] = N_ACTION;
		$data['ctime'] = time();
		$this->insert('db_sql_error_log',$data);
		throw new PException($error[2],999);
	}
	
	public function debug() {

		$rs = $this->select("show profiles");
		$totalQuery = 0;
		$totalTime = 0;
		$dStr = '';
		if(!empty($rs)){
			foreach ($rs as $key => $val) {
				$totalTime += $val['Duration'];
				if ($val['Duration']<0.001) {
					$color = 'green';
				}elseif($val['Duration']<0.01){
					$color = 'blue';
				}elseif($val['Duration']<0.1){
					$color = 'orange';
				}else{
					$color = 'red';
				}
				$totalQuery++;
				$dStr .= '<div><span style="color:green;">'.'序号：'.$val['Query_ID']."</span>　　运行时间：<span style=\"color:{$color}\">".$val['Duration'].'</span><br /><span style="color:#ccc">'.$val['Query'].'</span></div>';
			}
		}
		echo '总共执行sql数：'.$totalQuery.'<br>';
		echo '总共执行时间：'.$totalTime.'<br>';
		echo $dStr;

	}	










	

}
